#!/usr/bin/env python3
"""
修改 ks_material_rank 表结构，添加新字段
"""

import pymysql
from config import config
from password_utils import decrypt_password

def update_db_schema():
    # 获取数据库配置
    db_config = config.get('database')
    
    # 解密密码
    password = decrypt_password(db_config['password'])
    
    # 连接数据库
    conn = pymysql.connect(
        host=db_config['host'],
        port=db_config['port'],
        user=db_config['username'],
        password=password,
        database=db_config['database'],
        charset='utf8mb4'
    )
    
    try:
        cursor = conn.cursor()
        
        # 需要添加的新列定义
        new_columns = [
            ("mainIndustry", "ALTER TABLE ks_material_rank ADD COLUMN mainIndustry varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '所属行业'"),
            ("categoryLevel1Name", "ALTER TABLE ks_material_rank ADD COLUMN categoryLevel1Name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '一级分类'"),
            ("categoryLevel2Name", "ALTER TABLE ks_material_rank ADD COLUMN categoryLevel2Name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '二级分类'"),
            ("categoryLevel3Name", "ALTER TABLE ks_material_rank ADD COLUMN categoryLevel3Name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '三级分类'"),
            ("costTotal", "ALTER TABLE ks_material_rank ADD COLUMN costTotal bigint NULL COMMENT '消耗总额'"),
            ("adItemImpression", "ALTER TABLE ks_material_rank ADD COLUMN adItemImpression bigint NULL COMMENT '曝光量'"),
            ("ctr", "ALTER TABLE ks_material_rank ADD COLUMN ctr decimal(10, 6) NULL COMMENT '点击率'"),
            ("cvr", "ALTER TABLE ks_material_rank ADD COLUMN cvr decimal(10, 6) NULL COMMENT '转化率'"),
            ("ueScore", "ALTER TABLE ks_material_rank ADD COLUMN ueScore decimal(10, 6) NULL COMMENT '体验分'"),
            ("itemId", "ALTER TABLE ks_material_rank ADD COLUMN itemId bigint NULL COMMENT '商品ID'"),
            ("itemCoverUrl", "ALTER TABLE ks_material_rank ADD COLUMN itemCoverUrl varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '商品封面'"),
            ("photoCaption", "ALTER TABLE ks_material_rank ADD COLUMN photoCaption text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '素材描述'"),
            ("itemLinkUrl", "ALTER TABLE ks_material_rank ADD COLUMN itemLinkUrl varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '商品链接'"),
        ]
        
        # 首先检查现有列
        cursor.execute("DESCRIBE ks_material_rank")
        existing_columns = {row[0] for row in cursor.fetchall()}
        
        print(f"现有列: {existing_columns}\n")
        
        # 添加缺失的列
        for col_name, sql in new_columns:
            if col_name not in existing_columns:
                try:
                    print(f"添加列: {col_name}")
                    cursor.execute(sql)
                    print(f"  ✓ 成功")
                except Exception as e:
                    print(f"  ✗ 失败: {e}")
            else:
                print(f"列 {col_name} 已存在，跳过")
        
        conn.commit()
        print("\n表结构更新完成！")
        
    except Exception as e:
        print(f"错误: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    update_db_schema()
